<?php 

require_once('Class/connexionDB.Class.php');
/**
	Class used to get reporting data (=>statistics)
*/
class reporting
{
	var $date_start	= '';
	var $date_end	= '';
	var $country 	= '';
	var $group 		= ''; 
	var $team 		= '';
	var $nameRater 	= '';
	var $parameters = array();
	var $query 		= '';
	var $type 		= '';
	var $consultant = '';
	var $id_rater	= 0;
	var $id_manager = 0;
	var $form_id 	= 0;

	function setData($data)
	{
		if (isset($data['form_id']) && $data['form_id'])
		{
			$this->form_id = $data['form_id'];
		}

		if (isset($data['consultant_name']) && $data['consultant_name'] != '')
		{
			$this->consultant = $data['consultant_name'];
		}
		if (isset($data['type']) && $data['type'] != '')
		{
			$this->type = $data['type'];
		}

		if (isset($data['date_start']) && $data['date_start'] != '')
		{
			$this->date_start = $data['date_start'];
		}

		if (isset($data['date_end']) && $data['date_end'] != '')
		{
			$this->date_end = $data['date_end'];
		}

		if (isset($data['id_country']) && $data['id_country'] != '')
		{
			$this->country = $data['id_country'];
		}

		if (isset($data['id_name_rater']) && $data['id_name_rater'] != '')
		{
			$this->id_rater = $data['id_name_rater'];
		}

		if (isset($data['id_group']) && $data['id_group'] != '')
		{
			$db = new connexionDB();
			$result = $db->getGroupNameByID(intval($data['id_group']));
			if (isset($result[0]['name']))
			{
				$this->group = $result[0]['name'];	
			}
			else
			{
				$this->group = '';
			}
		}

		if (isset($data['team']) && $data['team'] != '')
		{
			$this->team = $data['team'];
		}

		if (isset($data['name_rater']) && $data['name_rater'] != '')
		{
			$this->nameRater = $data['name_rater'];
		}

		if (isset($data['id_manager']) && $data['id_manager'] != '')
		{
			$this->id_manager = intval($data['id_manager']);
		}
	}

	function setParameters()
	{
		if ($this->date_start != '')
		{
			$this->parameters[':date_start'] = $this->date_start;
		}
		if ($this->date_end != '')
		{
			$this->parameters[':date_end'] = $this->date_end;
		}
		if ($this->country != '')
		{
			$this->parameters[':country'] = $this->country;
		}
		if ($this->group != '')
		{
			$this->parameters[':group'] = '%' . $this->group . '%';
		}
		if ($this->team != '')
		{
			$this->parameters[':team'] = $this->team;
		}
		if ($this->nameRater != '')
		{
			$this->parameters[':nameRater'] = $this->nameRater;
		}
		if ($this->consultant != '')
		{
			$this->parameters[':consultant'] = $this->consultant;
		}
		if ($this->id_rater != '')
		{
			$this->parameters[':id_rater'] = $this->id_rater;
		}
		if ($this->id_manager != 0)
		{
			$this->parameters[':id_manager'] = $this->id_manager;
		}

		if ($this->form_id != 0)
		{
			$this->parameters[':id_evaluation_form'] = $this->form_id;
		}
	}

	function insertParameters()
	{
		if (isset($this->parameters[':date_end']) && isset($this->parameters[':date_start']) && $this->parameters[':date_start'] != '' && $this->parameters[':date_end'] != '')
		{
			$this->query .= "	`rating`.`date_eval` >= :date_start AND 
						`rating`.`date_eval` <= :date_end";
		}
		else
		{
			$this->query .= "	`rating`.`date_eval` >='2014/01/01' AND 
						`rating`.`date_eval` <= '". date('Y/m/d') ."'";	
		}

		if (isset($this->parameters[':country']) && $this->parameters[':country'] != '')
		{
			$this->query .= "  AND `rating`.`id_country`= :country ";
		}

		if (isset($this->parameters[':group']) && $this->parameters[':group'] != '')
		{
			$this->query .= " AND `rating`.`groups` LIKE :group ";
		}

		if (isset($this->parameters[':team']) && $this->parameters[':team'] != '')
		{
			$this->query .= " AND `rating`.`team` LIKE :team";
		}

		if (isset($this->parameters[':nameRater']))
		{
			$this->query .= " AND `rating`.`name_rater` LIKE :nameRater ";
		}

		if (isset($this->parameters[':consultant']))
		{
			$this->query .= " AND `rating`.`name_consultant` LIKE :consultant ";
		}

		if (isset($this->id_rater) && $this->id_rater != 0)
		{
			$this->query .= " AND `account`.`id` LIKE :id_rater ";
		}

		if (isset($this->id_manager) && $this->id_manager != 0)
		{
			$this->query .= " AND `rating`.`id_account_rater` = :id_manager ";
		}

		if (isset($this->parameters[':id_evaluation_form']))
		{
			$this->query .= " AND `criteria`.`id_evaluation_form` = :id_evaluation_form ";
		}
	}

	/**
		4 slices : 	0  -> 25
					25 -> 50
					50 -> 75
					75 -> 100
	*/
	function countDistribution($data)
	{
		$db 	= new connexionDB();
		$slice 	= array();

		$this->setData($data);
		$this->setParameters();
		$select_query = "SELECT
							COUNT(DISTINCT `rating`.`id`) AS `count`,
                            `evaluation_form`.`name` AS `eval_form`
						FROM
							`rating` LEFT JOIN `country` ON `rating`.`id_country`=`country`.`id`
                        LEFT JOIN 
                        	`rating_criteria` ON `rating_criteria`.`id_rating` = `rating`.`id`
                        LEFT JOIN 
                        	`criteria` ON `rating_criteria`.`id_criteria`=`criteria`.`id`
                        LEFT JOIN 
                        	`evaluation_form` ON `evaluation_form`.`id` = `criteria`.`id_evaluation_form`
						WHERE ";

		//Count first slice 0 -> 25
		$this->query = $select_query;
		$this->insertParameters();
		$this->query .= " AND `rating`.`final_mark` >= 0 AND `rating`.`final_mark` <= 25";
		
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		
		if (isset($result[0]['count']))
		{
			$slice['first'] = $result[0];
		}
		else
		{
			$slice['first'] = 0;	
		}

		//Count second slice 25 -> 50
		$this->query = $select_query;
		$this->insertParameters();
		$this->query .= " AND `rating`.`final_mark` > 25 AND `rating`.`final_mark` <= 50";
		
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		
		if (isset($result[0]['count']))
		{
			$slice['second'] = $result[0];
		}
		else
		{
			$slice['second'] = 0;	
		}

		//Count third slice 50 -> 75
		$this->query = $select_query;
		$this->insertParameters();
		$this->query .= " AND `rating`.`final_mark` > 50 AND `rating`.`final_mark` <= 75";
		
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		
		if (isset($result[0]['count']))
		{
			$slice['third'] = $result[0];
		}
		else
		{
			$slice['third'] = 0;	
		}

		//Count Fourth slice 75 -> 100
		$this->query = $select_query;
		$this->insertParameters();
		$this->query .= " AND `rating`.`final_mark` > 75 AND `rating`.`final_mark` <= 100";
		
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		
		if (isset($result[0]['count']))
		{
			$slice['fourth'] = $result[0];
		}
		else
		{
			$slice['fourth'] = 0;	
		}
		echo json_encode($slice);
	}

	function setQuery()
	{
		$this->query = "SELECT 
					`rating`.`date_eval`, 
					`country`.`name`, 
					`rating`.`groups`, 
					`rating`.`team`, 
					`rating`.`name_rater`,
					`evaluation_form`.`name` AS `eval_name`,
					";

		if ($this->type == 'nb')
		{
			$this->query .= "COUNT(DISTINCT `rating`.`id`) AS `nb_rating` ";
		}
		else
		{
			$this->query .= "AVG(`rating`.`final_mark`) AS `nb_rating` ";
		}


		$this->query .= "FROM 
							`rating` 
						LEFT JOIN `country` ON `rating`.`id_country`=`country`.`id` 
						LEFT JOIN `rating_criteria` ON `rating_criteria`.`id_rating`=`rating`.`id`
						LEFT JOIN `criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria`
						LEFT JOIN `evaluation_form` ON `criteria`.`id_evaluation_form`=`evaluation_form`.`id` 
				WHERE ";
		
		$this->insertParameters();
		$this->query .= " GROUP BY `rating`.`id_country`, `rating`.`groups`, `evaluation_form`.`id` ORDER BY `rating`.`groups`";
	}

	function calculateTotal($result)
	{
		$total 	= 0;
		$div 	= count($result);

		foreach ($result as $value)
		{
			if (isset($value['nb_rating']))
			{
				$total += $value['nb_rating'];
			}
		}
		if ($this->type == 'nb')
		{
			return $total;	
		}
		else
		{
			if ($div != 0)
			{
				return round($total / $div, 2);	
			}
			else
			{
				return 0;
			}
		}
	}

	function getNbAverage($data)
	{
		$db 	= new connexionDB();

		$this->setData($data);
		$this->setParameters();
		$this->setQuery();
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		$result['total'] = $this->calculateTotal($result);
		echo json_encode($result);
	}

	function nbRatingOverAverage()
	{
		$db 		= new connexionDB();
		$nb_rating	= array();

		$this->query = "SELECT 
						COUNT(DISTINCT `rating`.`id`) AS `nb_rating`, 
						`evaluation_form`.`name` AS `eval_name`
					FROM 
						`rating`
					LEFT JOIN 
						`rating_criteria` ON `rating_criteria`.`id_rating`=`rating`.`id`
					LEFT JOIN 
						`criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria`
					LEFT JOIN 
						`evaluation_form` ON `criteria`.`id_evaluation_form`=`evaluation_form`.`id`
					WHERE
						";
		$this->insertParameters();
		$this->query .= " AND `rating`.`final_mark` >= 50 ";
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		if (isset($result[0]['nb_rating']))
		{
			$nb_rating['over'] = $result[0];
		}
		else
		{
			$nb_rating['over'] = 0;
		}

		$this->query = "SELECT 
						COUNT(DISTINCT `rating`.`id`) AS `nb_rating`, 
						`evaluation_form`.`name` AS `eval_name`
					FROM 
						`rating`
					LEFT JOIN 
						`rating_criteria` ON `rating_criteria`.`id_rating`=`rating`.`id`
					LEFT JOIN 
						`criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria`
					LEFT JOIN 
						`evaluation_form` ON `criteria`.`id_evaluation_form`=`evaluation_form`.`id`
					WHERE  
						";
		$this->insertParameters();
		$this->query .= " AND `rating`.`final_mark` < 50 ";
		
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		
		if (isset($result[0]['nb_rating']))
		{
			$nb_rating['under'] = $result[0];
		}
		else
		{
			$nb_rating['under'] = 0;
		}
		return $nb_rating;
	}

	function getOverFifty($data)
	{
		$this->setData($data);
		$this->setParameters();
		$result = $this->nbRatingOverAverage();
		echo json_encode($result);
	}

	function getAverageFinalMark()
	{
		$db = new connexionDB();

		$this->query = 'SELECT 
							`rating`.`groups`, 
							ROUND(AVG(`rating`.`final_mark`), 2) AS `average` 
						FROM 
							`rating` 
						LEFT JOIN 
							`rating_criteria` ON `rating_criteria`.`id_rating`=`rating`.`id`
						LEFT JOIN 
							`criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria`
						LEFT JOIN 
							`evaluation_form` ON `criteria`.`id_evaluation_form`=`evaluation_form`.`id`
						WHERE  ';
		$this->insertParameters();
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		if (isset($result[0]))
		{
			return $result[0];	
		}
		else
		{
			return array();
		}
	}

	function getGroupAverageFinalMark($group)
	{
		$db 		= new connexionDB();
		$parameters = array();

		$query = 'SELECT 
					ROUND(AVG(`rating`.`final_mark`), 2) AS `average_group`
				FROM 
					`rating` 
				WHERE 
					`rating`.`groups` LIKE :group AND 
					`rating`.`date_eval` >= :date_start AND
					`rating`.`date_eval` <= :date_end';

		if (isset($group) && $group != '')
		{
			$parameters[':group'] = $group;
		}
		if (isset($this->date_end) && $this->date_end != '')
		{
			$parameters[':date_end'] = $this->date_end;
		}
		if (isset($this->date_start) && $this->date_start != '')
		{
			$parameters[':date_start'] = $this->date_start;
		}
		if (isset($group) && $group != '')
		{
			$result = $db->executePreparedQuery($query, $parameters);	
		}
		if (isset($result[0]['average_group']))
		{
			return $result[0];	
		}
		else
		{
			return array( 'average_group' => '');
		}
	}

	function getAverageMarkByGroup($data)
	{
		$this->setData($data);
		$this->setParameters();
		$result = array();

		$avg 		= $this->getAverageFinalMark();
		if (isset($avg['groups']))
		{
			$groupAvg 					= $this->getGroupAverageFinalMark($avg['groups']);
			$result['consultant_mark'] 	= $avg['average'];
			$result['group_mark'] 		= $groupAvg['average_group'];
			$result['group'] 			= $avg['groups'];
		}
		echo json_encode($result);
	}

	function getAverageFinalMarkFromRater($data)
	{
		$db 			= new connexionDB();
		
		$this->query = "SELECT 
					ROUND(AVG(`rating`.`final_mark`), 2) AS `average_team`
				FROM 
					`rating` 
				WHERE ";
		$this->insertParameters();
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		if (isset($result[0]))
		{
			return $result[0];	
		}
		else
		{
			return 0;
		}
	}

	function getAverageMarkFromRater($data)
	{
		$this->setData($data);
		$this->setParameters();
		$average = $this->getAverageFinalMarkFromRater($data);
	}

	function getAverageMarkCriteria($data)
	{
		$db 		= new connexionDB();

		$this->setData($data);
		$this->setParameters();

		$query = "SELECT 
					AVG(`criteria_mark`.`mark`) AS `averageMark`, 
					`criteria`.`name` AS `criteriaName`,
					`evaluation_form`.`name`,
					`rating`.`name_consultant` AS `consultantName`,
					`rating`.`date_eval` AS `dateEval`,
					`rating`.`groups`,
					`rating`.`team`,
					`rating`.`name_rater`
				FROM 
					`rating` LEFT JOIN 
					`rating_criteria` ON `rating`.`id`=`rating_criteria`.`id_rating` LEFT JOIN 
					`criteria_mark` ON `criteria_mark`.`id`=`rating_criteria`.`id_criteria_mark` LEFT JOIN 
					`criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria` LEFT JOIN 
					 `evaluation_form` ON `evaluation_form`.`id`=`criteria_mark`.`id_evaluation_form`
				WHERE 
					`rating`.`date_eval` >= :date_start AND
					`rating`.`date_eval` <= :date_end ";
		if ($this->consultant != '')
		{
			$query .= " AND `rating`.`name_consultant` LIKE :consultant ";	
		}
		
		if ($this->group != '')
		{
			$query .= " AND `rating`.`groups` LIKE :group ";	
		}
		
		if ($this->country != '')
		{
			$query .= " AND `rating`.`id_country`=:country ";	
		}

		if ($this->team != '')
		{
			$query .= " AND `rating`.`team` LIKE :team ";
		}

		if ($this->nameRater != '')
		{
			$query .= " AND `rating`.`name_rater` LIKE :nameRater ";
		}

		if ($this->form_id != 0)
		{
			$query .= " AND `evaluation_form`.`id` LIKE :id_evaluation_form ";
		}

		$query .= " GROUP BY 
					 `criteriaName`, `evaluation_form`.`name`, `criteria`.`id` ORDER BY `criteria`.`id` ASC";
		$result = $db->executePreparedQuery($query, $this->parameters);
		echo json_encode($result);
	}

	function getNbUnacceptable($data)
	{
		$db 		= new connexionDB();

		$this->setData($data);
		$this->setParameters();

		$this->query = "SELECT 
					count(*) AS `nbUnacceptable`, 
                    `criteria`.`name` AS `criteriaName`,
                    `rating`.`groups`,
                    `rating`.`team`,
                    `rating`.`name_rater`,
                    `rating`.`name_consultant`,
                    `evaluation_form`.`name` AS `eval_name`
				FROM 
					`rating` LEFT JOIN 
					`rating_criteria` ON `rating`.`id`=`rating_criteria`.`id_rating` LEFT JOIN 
					`criteria_mark` ON `criteria_mark`.`id`=`rating_criteria`.`id_criteria_mark` LEFT JOIN 
					`criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria` LEFT JOIN 
					 `evaluation_form` ON `evaluation_form`.`id`=`criteria_mark`.`id_evaluation_form`
                 WHERE
                 	";
        $this->insertParameters();
        $this->query .= " AND `id_unacceptable` <> 0 GROUP BY `criteria`.`name`";

        $result  = $db->executePreparedQuery($this->query, $this->parameters);
		echo json_encode($result);
	}

	function getNbRating($data)
	{
		$db 	= new connexionDB();
		$this->setData($data);
		$this->setParameters();

		$this->query = "SELECT 
							COUNT(DISTINCT `rating`.`id`) AS `nb_rating`
						FROM 
							`rating`
						LEFT JOIN 
							`rating_criteria` ON `rating_criteria`.`id_rating`=`rating`.`id`
						LEFT JOIN 
							`criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria`
						LEFT JOIN 
							`evaluation_form` ON `criteria`.`id_evaluation_form`=`evaluation_form`.`id`
						WHERE ";
		$this->insertParameters();
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		$result['query'] = $this->query;
		echo json_encode($result);
	}

	function getNbCalibration($data)
	{
		$db 	= new connexionDB();

		$this->setData($data);
		$this->setParameters();
		$this->query = "SELECT 
							`calibration`.`id`, 
							`calibration`.`id_account`, 
					        `calibration`.`groups`, 
					        `account`.`team`,
					        `account`.`firstname`,
					        `account`.`lastname`,
					        COUNT(*) as `nb_calibration`
						FROM 
							`calibration` LEFT JOIN `account` ON `account`.`id`=`calibration`.`id_account`
						WHERE
							";
		if (isset($this->date_end) && isset($this->date_start))
		{
			$this->query .= " `calibration`.`date` >= :date_start AND `calibration`.`date` <= :date_end ";
		}

		if (isset($this->group) && $this->group != '')
		{	
			$this->query .= " AND `calibration`.`groups` LIKE :group ";
		}

		if(isset($this->id_rater) && $this->id_rater != 0)
		{
			$this->query .= " AND `account`.`id` = :id_rater";
		}

		if (isset($this->team) && $this->team != '')
		{
			$this->query .= " AND `account`.`team` = :team ";
		}

		$this->query .= " GROUP BY `calibration`.`groups`, `account`.`team` ";
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		echo json_encode($result);
	}

	function getListedMarkConsultant($data)
	{
		$db 	= new connexionDB();

		$this->setData($data);
		$this->setParameters();
		$this->query = "SELECT
							`rating`.`id`,
							`rating`.`date_eval`,
							`rating`.`name_consultant`,
					        `rating`.`final_mark`,
					        `rating`.`name_rater`
						FROM 
							`rating`
							LEFT JOIN `rating_criteria` ON `rating`.`id`= `rating_criteria`.`id_rating` 
                            LEFT JOIN `criteria` ON `criteria`.`id` = `rating_criteria`.`id_criteria`
						WHERE 
							";
		$this->insertParameters();
		$this->query .= " GROUP BY 
							`rating`.`id` 
						ORDER BY 
							`rating`.`name_consultant` ASC";
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		/*$result["query"] = $this->query;*/
		echo json_encode($result);
	}

	function getAssociateCriteria($data)
	{
		$result = array();

		if (isset($data['id_calibration']) && $data['id_calibration'] != '')
		{
			$db 				= new connexionDB();
			$id_calibration 	= intval($data['id_calibration']);

			$this->query = "SELECT 
								`criteria`.`name`
							FROM 
								`criteria`
								LEFT JOIN `rating_criteria` ON `rating_criteria`.`id_criteria` = `criteria`.`id` 
								LEFT JOIN `calibration_rating` ON `calibration_rating`.`id_rating` = `rating_criteria`.`id_rating`

							WHERE 
								`calibration_rating`.`id_calibration` = :id_calibration
							GROUP BY
								`criteria`.`description`
							ORDER BY 
								`criteria`.`id`";
			$result = $db->executePreparedQuery($this->query, array(':id_calibration' => $id_calibration));
		}

		echo json_encode($result);
	}

	function getCriteriaMarkByIdCalibration($data)
	{
		$result = array();

		if (isset($data['id_calibration']))
		{
			$db = new connexionDB();
			$id_calibration = intval($data['id_calibration']);

			$ids_rating = $db->getIdRatingByIdCalibration($id_calibration);

			foreach ($ids_rating as $value)
			{
				$result[] = $db->getCriteriaMarkByIdRating($value['id_rating']);
			}
		}

		echo json_encode($result);
	}

	function getNbRejectedDebrief($data)
	{
		$db = new connexionDB();
		$this->setData($data);
		$this->setParameters();

		$this->query = "SELECT 
							`rating`.`groups` as `grp`, 
							`rating`.`id_account_rater`, 
							`account`.`firstname` AS `firstname_rater`, 
							`account`.`lastname` AS `lastname_rater`, 
							(SELECT COUNT(`rating`.`id`) FROM `rating` WHERE `rating`.`progress`=100 AND `rating`.`reject_debrief`=1 AND `rating`.`groups`=`grp`) AS `nb_rating_rejected`, 
							(SELECT COUNT(`rating`.`id`) FROM `rating` WHERE `rating`.`progress`=100 AND `rating`.`reject_debrief`=0 AND `rating`.`groups`=`grp`) AS `nb_rating_validated`, 
							(SELECT COUNT(`rating`.`id`) FROM `rating` WHERE `rating`.`progress`= 100 AND `rating`.`groups`=`grp`) AS `total_debrief`
						FROM 
							`rating` LEFT JOIN `account` ON `rating`.`id_account_rater`=`account`.`id` 
						WHERE 
						"; 
		$this->insertParameters();
		$this->query .= "	AND `rating`.`progress`=100
						GROUP BY 
							`rating`.`groups`";
		$result = $db->executePreparedQuery($this->query, $this->parameters);
		echo json_encode($result);
	}
}

?>